package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.db.Table;

import db.beans.M_WebsiteBean;

public class M_WebsiteTable extends Table{

	public static final String TABLE_NAME = "M_WEBSITE";

	public static final String COL_WEBSITE_ID = "WEBSITE_ID";
	public static final String COL_WEBSITE_NAME = "WEBSITE_NAME";
	public static final String COL_URL = "URL";
	public static final String COL_CURRENCY_ID = "CURRENCY_ID";
	public static final String COL_LANGUAGE_ID = "LANGUAGE_ID";
	public static final String COL_PRODUCT_TABLE_NAME= "PRODUCT_TABLE_NAME";
	public static final String COL_SEARCH_KEY_WORD_TABLE_NAME = "SEARCH_KEY_WORD_TABLE_NAME";
	public static final String COL_DELETE_FLAG = "DELETE_FLAG";
	public static final String F_COL_WEBSITE_ID = TABLE_NAME + "."+ "WEBSITE_ID";
	public static final String F_COL_WEBSITE_NAME = TABLE_NAME + "."+ "WEBSITE_NAME";
	public static final String F_COL_URL = TABLE_NAME + "."+ "URL";
	public static final String F_COL_CURRENCY_ID = TABLE_NAME + "."+ "CURRENCY_ID";
	public static final String F_COL_LANGUAGE_ID = TABLE_NAME + "."+ "LANGUAGE_ID";
	public static final String F_COL_PRODUCT_TABLE_NAME= TABLE_NAME + "."+ "PRODUCT_TABLE_NAME";
	public static final String F_COL_SEARCH_KEY_WORD_TABLE_NAME = TABLE_NAME + "."+ "SEARCH_KEY_WORD_TABLE_NAME";
	public static final String F_COL_DELETE_FLAG = TABLE_NAME + "."+ "DELETE_FLAG";


	public static M_WebsiteBean[] selectAll(Connection conn) throws SQLException{
		final String sql = "SELECT "
			+ COL_WEBSITE_ID + ","
			+ COL_WEBSITE_NAME + ","
			+ COL_URL + ","
			+ COL_DELETE_FLAG + ","
			+ COL_PRODUCT_TABLE_NAME + ","
			+ COL_CURRENCY_ID
			+ " FROM " + TABLE_NAME
			;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<M_WebsiteBean> v = new Vector<M_WebsiteBean> ();
        try{
    		stmt = conn.prepareStatement(sql);
    		rs = stmt.executeQuery();
    		int i;
            while( rs.next() ) {
            	i = 0;
            	M_WebsiteBean rec = new M_WebsiteBean();
            	rec.setWebsiteId(rs.getInt(++i));
            	rec.setWebsiteName(rs.getString(++i));
            	rec.setUrl(rs.getString(++i));
            	rec.setDeleteFlag(rs.getInt(++i)== NOT_DELETE_FLAG);
            	rec.setProductTableName(rs.getString(++i));
            	rec.setCurrencyId(rs.getInt(++i));
            	v.add(rec);
            }
        } finally {
            if(rs != null){
                rs.close();
            }

            if (stmt != null){
            	stmt.close();
            }
        }
        return (M_WebsiteBean[])v.toArray(new M_WebsiteBean[v.size()]);
	}

	public static M_WebsiteBean selectByWebsiteName(Connection conn, String websiteName) throws SQLException{
		final String sql = "SELECT "
			+ COL_WEBSITE_ID + ","
			+ COL_WEBSITE_NAME + ","
			+ COL_URL + ","
			+ COL_DELETE_FLAG + ","
			+ COL_PRODUCT_TABLE_NAME + ","
			+ COL_CURRENCY_ID
			+ " FROM " + TABLE_NAME
			+ " WHERE "
			+ COL_WEBSITE_NAME + "=?"
			;
		PreparedStatement stmt = null;
		ResultSet rs = null;
    	M_WebsiteBean rec = null;
        try{
    		stmt = conn.prepareStatement(sql);
    		int i = 0;
    		stmt.setString(++i, websiteName);
    		rs = stmt.executeQuery();
            while( rs.next() ) {
            	i = 0;
            	rec = new M_WebsiteBean();
            	rec.setWebsiteId(rs.getInt(++i));
            	rec.setWebsiteName(rs.getString(++i));
            	rec.setUrl(rs.getString(++i));
            	rec.setDeleteFlag(rs.getInt(++i)== NOT_DELETE_FLAG);
            	rec.setProductTableName(rs.getString(++i));
            	rec.setCurrencyId(rs.getInt(++i));
            }
        } finally {
            if(rs != null){
                rs.close();
            }

            if (stmt != null){
            	stmt.close();
            }
        }
        return rec;
	}


	public static double getRateByWebsiteName(Connection conn, String srcWebsiteName) throws SQLException{
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT ");
		sqlBuf.append(M_CurrencyRateTable.COL_RATE);
		sqlBuf.append(" FROM ");
		sqlBuf.append(M_CurrencyRateTable.TABLE_NAME);
		sqlBuf.append(",");
		sqlBuf.append(M_CurrencyTable.TABLE_NAME);
		sqlBuf.append(",");
		sqlBuf.append(M_WebsiteTable.TABLE_NAME);
		sqlBuf.append(" WHERE ");
		sqlBuf.append(M_WebsiteTable.COL_WEBSITE_NAME + "=?");
		sqlBuf.append(" AND ");
		sqlBuf.append(M_WebsiteTable.COL_CURRENCY_ID + "=" + M_CurrencyTable.COL_CURRENCY_ID);
		sqlBuf.append(" AND ");
		sqlBuf.append(M_CurrencyTable.COL_CURRENCY_ID + "=" + M_CurrencyRateTable.COL_CURRENCY_ID);

		PreparedStatement stmt = null;
		ResultSet rs = null;
    	int rate = 1;
        try{
    		stmt = conn.prepareStatement(sqlBuf.toString());
    		stmt.setString(1, srcWebsiteName);
    		rs = stmt.executeQuery();
            if( rs.next()) {
            	rate = rs.getInt(1);
            }
        } finally {
            if(rs != null){
                rs.close();
            }

            if (stmt != null){
            	stmt.close();
            }
        }
        return rate;
	}
}
